|
쿼리를 짜다보면 종종 100이상의 값들의 합만 따로 찾고 100미만의 값들의 합만 따로 찾아내라는 경우가 있다.
물론 간단하게
SELECT SUM(OVER100) AS OVER100
,SUM(DOWN100) AS DOWN100
FROM (
SELECT SUM(A) AS OVER100
,0 AS DOWN100
FROM TEST
WHERE A >= 100
UNION ALL
SELECT 0 AS OVER100
,SUM(A) AS DOWN100
FROM TEST
WHERE A < 100
)
이렇게 해주면 해결이 되나 솔직히 없어 보이기도 하고 무식한 방법인 UNION ALL 보다는 뭔가 더 간단하면서 심플한 방법이 있을지도 이런 생각을 하게 마련이다.
물론 오라클에서 함수들을 조합하면 위의 쿼리와 같은 효과를 낼수 있다.
SIGN과 DECODE(혹은 CASE) 함수를 이용하면 된다.
먼저 각 함수가 어떤건지 알아 둘 필요가 있다.
먼저 SIGN함수는 인수를 하나를 받고 그 인수값이 0보다 크면 1값을, 0이면 0, 0보다 작으면 -1값을 리턴한다.
예시1) SELECT SIGN(-100) AS RESULT1,
SIGN(0) AS RESULT2,
SIGN(50) AS RESULT3
FROM DUAL;
이렇게 실행하면 실행값은 아래와 같다.
|
RESULT1 |
RESULT2 |
RESULT3 |
|
-1 |
0 |
1 |
두번째로 DECODE함수는 데이터 들을 다른 값으로 바꾸어주는 함수인데 형식은 형식 DECODE(VALUE, IF1, THEN1, IF2, THEN2...)
이런 형식이다. 말보단 예시가 더 이해하기가 쉽다.
예시2) SELECT DECODE(1, 1, 100, 200) AS RESULT1
FROM DUAL;
이렇게 실행하면 결과값은
즉 DECODE 함수는 VALUE 의 값(1)에서 IF1의 값이 1이면 100값(THEN1)을 아니면 200값(THEN2)을 리턴을 한다.
지금은 간단하지만 DECODE(VALUE, IF1, THEN1, IF2, THEN2, IF3, THEN3, IF4, THEN4...) 이런식으로 무한확장이 가능하다.
근데 될수록 길게 쓰지 않는다.
마지막으로 CASE문이 있는데 이건 DECODE랑 쓰는 의미는 같다고 생각하면 된다.
예시2 의 쿼리를 CASE문으로 쓴다고 치면 다음과 같다.
예시3) SELECT CASE 1
WHEN 1 THEN 100
ELSE 200
END AS RESULT1
FROM DUAL;
물론 값은 예시2와 같다.
CASE문도 DECODE문과 같이 무학확장이 가능하다.
SELECT CASE 1
WHEN 1 THEN 100
WHEN 2 THEN 200
WHEN 3 THEN 300
WHEN 4 THEN 400
ELSE 500
END AS RESULT1
FROM DUAL;
이제 SIGN과 DECODE 이용해서 비교값의 합을 찾아보자
SELECT SUM(DECODE(SIGN(A-100), 0, A, 1, A, 0)) AS OVER100,
SUM(DECODE(SIGN(A-100), -1, A, 0)) AS DOWN100
FROM TEST;
맨위의 UNION ALL을 사용한 함수보다 훨씬 깔끔하게 쿼리라인이 줄어들었다.
설명 들어가자면 먼저 SIGN(A-100) 을 하면 A값이 100보다 크면 1값을 100이면 0값을 100보다 작으면 -1값을 가져온다.
그렇게 산출된 값을 DECODE문을 이용해서 0과 1값들만 가져와서 A값들의 합을 가져온다.
그 반대의 경우도 마찬가지
CASE문을 사용하면 이보다는 좀 더 복잡해진다.
SELECT SUM(CASE SIGN(A-100)
WHEN 0 THEN A
WHEN 1 THEN A
ELSE 0
END) AS OVER100,
SUM(CASE SIGN(A-100)
WHEN -1 THEN A
ELSE 0
END) AS DOWN100
FROM DUAL;
CASE 문도 DECODE와 같다.
보면은 DECODE가 쉬워보이지만 단점이 있는데 그건 IF 조건이 많으면 많을수록 DECODE로 쓴 쿼리는 지저분해지는 반면
CASE 문을 좀더 깔끔한 쿼리가 가능하다. 그리고 DECODE는 오라클에서만 지원하는 것이므로 범용적으로 사용할려면
CASE 문이 좋다.
쿼리는 짜면 짤수록 재미는 있다. 특히 어려운 쿼리를 단순하게 만들때의 쾌감은 이로 말할수가 없다..ㅋㅋ
쿼리 짤때 젤 중요한건 기술이 아니라 남에게 보여줘서 바로 이해가능한 쿼리가 진짜 좋은 쿼리라는거다.
모두다 DB 고수일수는 없는법... |
